﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using OpenSISCalenderDetailsBusinessLayer;
using OpenSISDataConnection;
using System.Globalization;

namespace OpenSISCalenderDetailsDataLayer
{
    /// <summary>
    /// Summary description for CalenderDetailsDL
    /// </summary>
    public class CalenderDetailsDL
    {
        public CalenderDetailsDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
       public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

       // public static string server = "MSSQLServer";

        public int AddCalenderDetails(CalenderDetailsBL CalenderDetails)
        {
            try
            {
              if (server == "MSSQLServer")
            {
               
                DataSet ds = null;
                DataSet dsSortOrder = null;
                object id;
                

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Caleder_Id,Calender_Details");

                dsSortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(sort_order),0) + 1 as sort_order from Calender_Details where Sch_Id='" + CalenderDetails.SchoolID + "'");

                String strAddCalDetails = "INSERT into Calender_Details(Caleder_Id,Default_Tag,Calender_Name,From_Date,To_Date,Sch_Id,Last_Update_Date,sort_order) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + CalenderDetails.DefaultTag + "',N'" + CalenderDetails.CalenderName.Replace("'", "''") + "',N'" + CalenderDetails.FromDate + "',N'" + CalenderDetails.ToDate + "'," + CalenderDetails.SchoolID + ",N'" + CalenderDetails.LastUpdated + "',N'" + Convert.ToInt32(dsSortOrder.Tables[0].Rows[0][0]) + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strAddCalDetails);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(Caleder_Id) from Calender_Details").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }
                if (server == "MySQL")
                {
                    DateTime s = Convert.ToDateTime(CalenderDetails.LastUpdated, DateTimeFormatInfo.InvariantInfo);
                    CalenderDetails.LastUpdated = s.ToString("yyyy/MM/dd");

                    DateTime d = Convert.ToDateTime(CalenderDetails.FromDate, DateTimeFormatInfo.InvariantInfo);
                    CalenderDetails.FromDate = d.ToString("yyyy/MM/dd");

                    DateTime e = Convert.ToDateTime(CalenderDetails.ToDate, DateTimeFormatInfo.InvariantInfo);
                    CalenderDetails.ToDate = e.ToString("yyyy/MM/dd");


                    DataSet ds = null;
                    DataSet dsSortOrder = null;
                    object id;


                   
                    ds = DataConnection.ConnectionPath.Getdata("CALL getmax('Caleder_Id','Calender_Details')");

                    dsSortOrder = DataConnection.ConnectionPath.Getdata("select IFNULL(max(sort_order),0) + 1 as sort_order from Calender_Details where Sch_Id='" + CalenderDetails.SchoolID + "'");

                    String strAddCalDetails = "INSERT into Calender_Details(Caleder_Id,Default_Tag,Calender_Name,From_Date,To_Date,Sch_Id,Last_Update_Date,sort_order) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + CalenderDetails.DefaultTag + "',N'" + CalenderDetails.CalenderName.Replace("'", "''") + "',N'" + CalenderDetails.FromDate + "',N'" + CalenderDetails.ToDate + "'," + CalenderDetails.SchoolID + ",N'" + CalenderDetails.LastUpdated + "',N'" + Convert.ToInt32(dsSortOrder.Tables[0].Rows[0][0]) + "')";

                    DataConnection.ConnectionPath.ExecuteCommand(strAddCalDetails);
                    id = DataConnection.ConnectionPath.Getdata("SELECT max(Caleder_Id) from Calender_Details").Tables[0].Rows[0][0];
                    return Convert.ToInt32(id);


                }
                else
                {
                    return 0;
                
                }

        }
           
             catch
            {
                return 0;
            }

           
        }

        public void UpdateCalenderDetails(CalenderDetailsBL CalenderDetails)
        {
            try
            {
                if (server == "MSSQLServer")
                {

                    string strUpdateCalDetails = "update Calender_Details set Default_Tag= N'" + CalenderDetails.DefaultTag + "',Calender_Name= N'" + CalenderDetails.CalenderName.Replace("'", "''") + "',From_Date= N'" + CalenderDetails.FromDate + "',To_Date= N'" + CalenderDetails.ToDate + "',Last_Update_Date= N'" + CalenderDetails.LastUpdated + "' where Caleder_Id=" + CalenderDetails.CalenderID + " and Sch_Id=" + CalenderDetails.SchoolID + "";

                    DataConnection.ConnectionPath.ExecuteCommand(strUpdateCalDetails);
                }
                if (server == "MySQL")
                {
                    DateTime s = Convert.ToDateTime(CalenderDetails.LastUpdated, DateTimeFormatInfo.InvariantInfo);
                    CalenderDetails.LastUpdated = s.ToString("yyyy/MM/dd");

                    DateTime d = Convert.ToDateTime(CalenderDetails.FromDate, DateTimeFormatInfo.InvariantInfo);
                    CalenderDetails.FromDate = d.ToString("yyyy/MM/dd");

                    DateTime e = Convert.ToDateTime(CalenderDetails.ToDate, DateTimeFormatInfo.InvariantInfo);
                    CalenderDetails.ToDate = e.ToString("yyyy/MM/dd");


                    string strUpdateCalDetails = "update Calender_Details set Default_Tag= N'" + CalenderDetails.DefaultTag + "',Calender_Name= N'" + CalenderDetails.CalenderName.Replace("'", "''") + "',From_Date= N'" + CalenderDetails.FromDate + "',To_Date= N'" + CalenderDetails.ToDate + "',Last_Update_Date= N'" + CalenderDetails.LastUpdated + "' where Caleder_Id=" + CalenderDetails.CalenderID + " and Sch_Id=" + CalenderDetails.SchoolID + "";

                    DataConnection.ConnectionPath.ExecuteCommand(strUpdateCalDetails);
                }
            }
            catch
            {
            }
        }

        public System.Data.DataTable PopulateCalenderDetails(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Calender_Details where sch_id=" + CalenderDetails.SchoolID + " order by sort_order").Tables[0];
            return dt;
        }

        public void UpdateCalenderDetailsShortOrder(CalenderDetailsBL CalenderDetails)
        {

            string strUpdateCalOrder = "update Calender_Details set sort_order= N'" + CalenderDetails.SortOrder.Replace("'", "''") + "' where Caleder_Id=" + CalenderDetails.CalenderID + "";

            DataConnection.ConnectionPath.ExecuteCommand(strUpdateCalOrder);

        }

        public void DeleteCalDetails(CalenderDetailsBL CalenderDetails)
        {

            string strCalDetailsDelete = "deleteCalenderDetails " + CalenderDetails.CalenderID + ","+ CalenderDetails.SchoolID +"";

            DataConnection.ConnectionPath.ExecuteCommand(strCalDetailsDelete);

        }


        //*****************************************************************************************************

        public void AddCalDaySchedule(CalenderDetailsBL CalenderDetails)
        {
            try
            {
                if (server == "MSSQLServer")
                {
                    DataSet ds = null;
                    ds = DataConnection.ConnectionPath.Getdata("exec getMax Schedule_Id,Calender_Day_Schedule");


                    String strAddCalDaySchedule = "INSERT into Calender_Day_Schedule(Schedule_Id,Calender_Id,School_Id,Schedule_Day,Start_Time,End_Time) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ", N'" + CalenderDetails.CalenderID + "', N'" + CalenderDetails.SchoolID + "', N'" + CalenderDetails.ScheduleDay + "', N'" + CalenderDetails.StartTime.Replace("'", "''") + "', N'" + CalenderDetails.EndTime.Replace("'", "''") + "')";
                    DataConnection.ConnectionPath.ExecuteCommand(strAddCalDaySchedule);
                }
                if (server == "MySQL")
                {
                    DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('Schedule_Id','Calender_Day_Schedule')");

                    String strAddCalDaySchedule = "INSERT into Calender_Day_Schedule(Schedule_Id,Calender_Id,School_Id,Schedule_Day,Start_Time,End_Time) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ", N'" + CalenderDetails.CalenderID + "', N'" + CalenderDetails.SchoolID + "', N'" + CalenderDetails.ScheduleDay + "', N'" + CalenderDetails.StartTime.Replace("'", "''") + "', N'" + CalenderDetails.EndTime.Replace("'", "''") + "')";
                    DataConnection.ConnectionPath.ExecuteCommand(strAddCalDaySchedule);
                }
            }
            catch
            {
            }
        }

        public void DeleteCalDaySchedule(CalenderDetailsBL CalenderDetails)
        {
            string strCalDayScheduleDelete = "delete from Calender_Day_Schedule where Calender_Id=" + CalenderDetails.CalenderID + " and School_Id=" + CalenderDetails.SchoolID + "";

            DataConnection.ConnectionPath.ExecuteCommand(strCalDayScheduleDelete);
        }

        public System.Data.DataTable PopulateCalenderDaySchedule(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Calender_Day_Schedule where School_Id=" + CalenderDetails.SchoolID + " and Calender_Id=" + CalenderDetails.CalenderID + "").Tables[0];
            return dt;
        }


        public System.Data.DataTable PopulateCalenderWeekEndDaySchedule(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Calender_WeekEnd_Schedule where School_Id=" + CalenderDetails.SchoolID + " and Calender_Id=" + CalenderDetails.CalenderID + "").Tables[0];
            return dt;
        }



        public System.Data.DataTable CheckCalenderWeekEndDay(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select WeekEnd_Day from Calender_WeekEnd_Schedule where WeekEnd_Day='" + CalenderDetails.CalWeekEndDay + "' and Calender_Id=" + CalenderDetails.CalenderID + " and School_Id="+ CalenderDetails.SchoolID +"").Tables[0];
            return dt;
        }


        //******************************30/07/09******************************

        public void AddCalWeekEndDaySchedule(CalenderDetailsBL CalenderDetails)
        {
            try
            {
                if (server == "MSSQLServer")
                {
                    DataSet ds = null;
                    ds = DataConnection.ConnectionPath.Getdata("exec getMax WeekEnd_id,Calender_WeekEnd_Schedule");



                    String strAddCalWeekEndDaySchedule = "INSERT into Calender_WeekEnd_Schedule(WeekEnd_id,Calender_Id,School_Id,WeekEnd_Day) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ", N'" + CalenderDetails.CalenderID + "', '" + CalenderDetails.SchoolID + "', N'" + CalenderDetails.CalWeekEndDay + "')";
                    DataConnection.ConnectionPath.ExecuteCommand(strAddCalWeekEndDaySchedule);
                }
                if (server == "MySQL")
                {
                    DataSet ds = null;
                   
             ds = DataConnection.ConnectionPath.Getdata("CALL getmax('WeekEnd_id','Calender_WeekEnd_Schedule')");


                    String strAddCalWeekEndDaySchedule = "INSERT into Calender_WeekEnd_Schedule(WeekEnd_id,Calender_Id,School_Id,WeekEnd_Day) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ", N'" + CalenderDetails.CalenderID + "', '" + CalenderDetails.SchoolID + "', N'" + CalenderDetails.CalWeekEndDay + "')";
                    DataConnection.ConnectionPath.ExecuteCommand(strAddCalWeekEndDaySchedule);
                }

            }
            catch
            {
            }
        }

        public void DeleteCalWeekEndDaySchedule(CalenderDetailsBL CalenderDetails)
        {
            string strCalWeekEndDayScheduleDelete = "delete from Calender_WeekEnd_Schedule where Calender_Id=" + CalenderDetails.CalenderID + " and School_Id=" + CalenderDetails.SchoolID + "";

            DataConnection.ConnectionPath.ExecuteCommand(strCalWeekEndDayScheduleDelete);
        }



        //Added on 19th august*************************************

        public System.Data.DataTable CheckHoliday(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select event_date from events where event_type='H' and event_parent_id=" + CalenderDetails.CalenderID + " and Sch_ID=" + CalenderDetails.SchoolID + "").Tables[0];
            return dt;
        }



        public System.Data.DataTable CheckSchoolDay(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;

            DateTime d = Convert.ToDateTime(CalenderDetails.EventDate, DateTimeFormatInfo.InvariantInfo);

            CalenderDetails.EventDate = d.ToString("yyyy/MM/dd", DateTimeFormatInfo.InvariantInfo);

            dt = DataConnection.ConnectionPath.Getdata("select event_date,event_type from events where event_type='S' and event_parent_id=" + CalenderDetails.CalenderID + " and Sch_ID=" + CalenderDetails.SchoolID + " and event_date = '" + CalenderDetails.EventDate + "' ").Tables[0];
            return dt;
        }



        //**********************************added on 4 th sept**************///


        public System.Data.DataTable CheckHolidayforschlevent(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;

            DateTime d = Convert.ToDateTime(CalenderDetails.EventDate, DateTimeFormatInfo.InvariantInfo);

            CalenderDetails.EventDate = d.ToString("yyyy/MM/dd", DateTimeFormatInfo.InvariantInfo);

            dt = DataConnection.ConnectionPath.Getdata("select event_id,event_date,event_type from events where event_type='H' and event_parent_id=" + CalenderDetails.CalenderID + " and Sch_ID=" + CalenderDetails.SchoolID + " and event_date = '" + CalenderDetails.EventDate + "' ").Tables[0];
            return dt;
        }




        public System.Data.DataTable Checkschlwrtweekend(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select event_date from events where event_type='S' and event_parent_id=" + CalenderDetails.CalenderID + " and Sch_ID=" + CalenderDetails.SchoolID + "").Tables[0];
            return dt;
        }


        //********26/04/2010*********

        public System.Data.DataTable ExistCallenderDetailsSchedule(CalenderDetailsBL CalenderDetails)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("ExistCalenderDetails " + CalenderDetails.CalenderID + "," + CalenderDetails.SchoolID + "").Tables[0];

            return dt;
        }

    }
}
